﻿/*
Project Name : Menfram
Module Name  : DAO
Date  : 07/06/2013
responsible : Mateus Neto
Obs  : The license of this software is Mozilla Public License 1.1 
contact : mentrixmax@gmail.com
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections;


namespace Menfram.Implementation.DAL
{
    /// <summary>
    /// Class responsible for the ability of the framework to connect to a Ms SqlServer database
    /// </summary>
    public class SQLDAO : Menfram.Implementation.DAL.IAbstractSQL
    {

        /// <summary>
        /// Method that creates a command to execute SQL queries
        /// </summary>
        public DbCommand Command { get; set; }


        /// <summary>
        /// Method that creates a connection with database
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DbConnection Connection
        {
            get
            {
                return getConnection();
            }
        }

        /// <summary>
        /// method used to mount the queries in the database
        /// </summary>
        public Hashtable listOfTheParam { get; set; }


        private String PConnectionUrl;
        /// <summary>
        /// Propertie to Connection Url with the database
        /// </summary>
        public String ConnectionUrl
        {
            get { return PConnectionUrl; }
            set { PConnectionUrl = value; }
        }

        /// <summary>
        /// method with get the transaction with the database
        /// </summary>
        public DbTransaction Transaction
        {
            get;
            set;
        }

        /// <summary>
        /// the method that set the parameteres 
        /// </summary>
        public void setParameteres()
        {
            Command.Parameters.Clear();
            SqlCommand com = (SqlCommand)Command;
            if (listOfTheParam != null)
            {
                foreach (string key in listOfTheParam.Keys)
                {
                    if (listOfTheParam[key] == null)
                    {

                        com.Parameters.AddWithValue(key, DBNull.Value);

                    }
                    else
                    {
                        com.Parameters.AddWithValue(key, listOfTheParam[key]);
                    }
                }
            }
        }


      
        public DbCommand getCommand(String sql)
        {
            Command = new SqlCommand(sql, (SqlConnection)Connection);
            return Command;
        }
        #region Atributos
        /// <summary>
        /// implementation default of the Singleton.
        /// </summary>
        private static SQLDAO instance = null;

        /// <summary>
        /// Connection Attribute;
        /// </summary>
        private static SqlConnection conn = null;

        #endregion

        /// <summary>
        /// Implementation of the Singleton
        /// </summary>
        /// <returns></returns>
        public IAbstractSQL getInstance()
        {
            if (instance == null)
            {
                instance = new SQLDAO();
            }
            return instance;
        }

        /// <summary>
        /// method which get the SLQConnection oppened.
        /// </summary>
        /// <returns></returns>
        public SqlConnection getConnection()
        {
            if (conn == null)
            {
                conn = new SqlConnection(ConnectionUrl);
            }
            this.openConnection();
            return conn;
        }

        /// <summary>
        /// method which open the Connection whith DB
        /// </summary>
        /// <returns></returns>
        private bool openConnection()
        {
            try
            {
                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                    return true;
                }

            }
            catch (SqlException e)
            {
                conn = null;
                throw e;
            }
            return false;
        }

        /// <summary>
        /// method which close the Connection with DB
        /// </summary>
        /// <returns></returns>
        public bool closeConnection()
        {
            try
            {
                if (conn != null && conn.State == System.Data.ConnectionState.Open)
                {
                    conn.Close();
                    conn = null;
                    return true;
                }

            }
            catch (SqlException e)
            {

                throw e;
            }
            return false;
        }

    }
}
